package com.eascs.admin.o2o.internal.dao;

import java.util.GregorianCalendar;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.common.utils.StringUtils;
import com.eascs.admin.o2o.internal.form.PayMentSearchForm;
import com.eascs.admin.o2o.internal.util.BaseUtils;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.sso.UserHolder;

/**
 * 
 * 贷款查询dao
 *
 */
@Repository
public class PayMentDao extends BaseO2oDao{
	/**
	 * 贷款分页查询
	 * @param payMentSearchForm
	 * @return
	 */
	public PageResult<Object[]> findByPage(PayMentSearchForm payMentSearchForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,payMentSearchForm.getPageSize() ,
				payMentSearchForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : payMentSearchForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" select t.loanapply_no,t.bill_code,zx.zx_bank,t.user_full_name,t.operator,t.ltd_name,t.province_name ");// 征信银行取o2o_zx表中的值zx.zx_bank
		sql.append(" ,t.loan_type,t.wf_type,t.bill_status,t.loana_mount/10000 loana_mount,t.create_date, t.bank_loan_name from ( ");
		sql.append(" select l.loanapply_no,(case when l.bill_code is null then '查看' else l.bill_code end) as 'bill_code', ");
		sql.append(" l.bank_type as 'bank_type',u.user_full_name as 'user_full_name',l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltd_name',opr.province_name AS 'province_name', l.loan_type as 'loan_type', ");
		sql.append(" (case l.credit_status when 0 then 'ZXSX' else l.wf_type end) as 'wf_type', ");
		sql.append(" e.bill_status as 'bill_status' , CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount ");
		sql.append(" ) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END) IS NULL THEN ");
		sql.append(" l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2)) as 'loana_mount', ");
		sql.append(" l.create_date as 'create_date',l.buyback_name,l.is_place_on_file,l.operation_model,opr.province_code,l.partner_id,l.bank_loan_name ");
		sql.append(" ,u.zx_no as 'zx_no' ");// 征信银行取o2o_zx表中的值zx.zx_bank
		sql.append(" from o2o_loanapply l LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id ");
		sql.append("  and u.spouse_id is null and u.sponsor_type is null ");
		sql.append("  LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd  ");
		sql.append("   LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no where 1=1");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(payMentSearchForm.getUsersno())) {
			sql.append(" or l.operator_no in ("+payMentSearchForm.getUsersno()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.islock='N' and l.loanapply_no=u.loanapply_no) t LEFT JOIN o2o_zx zx on t.zx_no = zx.id where 1=1 ");// 征信银行取o2o_zx表中的值zx.zx_bank
		
		if (StringUtil.hasText(payMentSearchForm.getBillCode())) {
			sql.append(" and t.bill_code like ?");
			pageQuery.addLikeParam(payMentSearchForm.getBillCode());
		}
		if (StringUtil.hasText(payMentSearchForm.getLoanType())) {
			sql.append(" and t.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(payMentSearchForm.getLoanType().contains(",")){
				loanTypeA = payMentSearchForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+payMentSearchForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(payMentSearchForm.getPayMentStatus())) {
			if ("O".equals(payMentSearchForm.getPayMentStatus())) {
				sql.append(" and t.wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(payMentSearchForm.getPayMentStatus())){
				sql.append(" and t.wf_type=0");
			}else {
				sql.append(" and t.wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (payMentSearchForm.getPayMentStatus().contains(",")) {
					wfTypeA = payMentSearchForm.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + payMentSearchForm.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				sql.append(wfType);
				sql.append(" ) ");
			}
		}
		if (StringUtil.hasText(payMentSearchForm.getOperatorName())) {
			sql.append(" and t.operator like ?");
			pageQuery.addLikeParam(payMentSearchForm.getOperatorName());
		}
		if (StringUtil.hasText(payMentSearchForm.getUserSurname())) {
			sql.append(" and t.user_full_name like ?");
			pageQuery.addLikeParam(payMentSearchForm.getUserSurname());
		}
		if (StringUtil.hasText(payMentSearchForm.getUserMohuName())) {
			sql.append(" and t.user_full_name = ?");
			pageQuery.addParam(payMentSearchForm.getUserMohuName());
		}
		if (StringUtil.hasText(payMentSearchForm.getBankType())) {
			sql.append(" and zx.zx_bank = ?");
			pageQuery.addParam(payMentSearchForm.getBankType());
		}
		if (StringUtil.hasText(payMentSearchForm.getBankloanname())) {
			sql.append(" and t.bank_loan_name = ?");
			pageQuery.addParam(payMentSearchForm.getBankloanname());
		}
		if (StringUtil.hasText(payMentSearchForm.getLoadName())) {
			sql.append(" and t.ltd_name like ?");
			pageQuery.addLikeParam(payMentSearchForm.getLoadName());
		}
		if (StringUtil.hasText(payMentSearchForm.getBuybackName())) {
			sql.append(" and t.buyback_name like ?");
			pageQuery.addLikeParam(payMentSearchForm.getBuybackName());
		}
		if (StringUtil.hasText(payMentSearchForm.getStartTime())) {
			sql.append(" and t.create_date >= ? ");
			pageQuery.addParam(payMentSearchForm.getStartTime());
		}
		if (StringUtil.hasText(payMentSearchForm.getEndTime())) {
			sql.append(" and t.create_date <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(payMentSearchForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(payMentSearchForm.getOnPlace())) {
			sql.append(" and t.is_place_on_file = ? ");
			pageQuery.addParam(payMentSearchForm.getOnPlace());
		}
		if (StringUtil.hasText(payMentSearchForm.getOperationModel())) {
			sql.append(" and t.operation_model = ? ");
			pageQuery.addParam(payMentSearchForm.getOperationModel());
		}
		//大区查询operation_model
		/*if(!StringUtil.isEmpty(payMentSearchForm.getRegionalName())){
			sql.append(" AND opr.regional_name  = ?");
			pageQuery.addParam(payMentSearchForm.getRegionalName());
		}*/
		if (!StringUtil.isEmpty(payMentSearchForm.getHostProvince())) {
			sql.append(" AND  t.province_code  = ? ");
			pageQuery.addParam(payMentSearchForm.getHostProvince());
		}
		if (!StringUtil.isEmpty(payMentSearchForm.getOtoBzjGlf())) {
			
			if (payMentSearchForm.getOtoBzjGlf().contains("NEW")) {
				sql.append(" AND  (t.bill_status is null or t.bill_status in ( ");
			} else {
				sql.append(" AND  t.bill_status in ( ");
			}
			
			// ******************** loan-12380 贷款申请查询中的缴费状态改为多选查询
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(payMentSearchForm.getOtoBzjGlf().contains(",")){
				loanTypeA = payMentSearchForm.getOtoBzjGlf().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+payMentSearchForm.getOtoBzjGlf()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(" ) ");
			if (payMentSearchForm.getOtoBzjGlf().contains("NEW")) {
				sql.append(" ) ");
			}
			//***********************
		}
		if(!StringUtil.isEmpty(payMentSearchForm.getPartnerId())){
			sql.append(" AND t.partner_id  in (");
			sql.append(payMentSearchForm.getPartnerId());
			sql.append(")");
		}
		sql.append(" order by t.create_date	desc ");
		pageQuery.setSumColumnIndexs(new int[]{10});
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	public PageResult<Object[]> findByPageExport(PayMentSearchForm payMentSearchForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,payMentSearchForm.getPageSize() ,
				payMentSearchForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : payMentSearchForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" select l.bill_code, (select d.item_name from PUB_DDITEM d where d.DD_NO=10340 and d.item_code= zx.zx_bank ) as 送审银行,");
		sql.append(" u.user_full_name as 'userName',l.operator_name as 'operator',CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',l.buyback_name as 'buybackName',l.commice_recommend,");
		sql.append(" (select d.item_name from PUB_DDITEM d where d.DD_NO=11321 and d.item_code= l.loan_Type ) as 贷款类型,");
		sql.append(" (select d.item_name from pub_dditem_o2o d where d.DD_NO=2 and d.item_code= l.wf_type ) as 申请状态,");
		sql.append(" (select d.item_name from pub_dditem_o2o d where d.DD_NO=5 and d.item_code= e.bill_status  ) as 缴纳状态,");
		sql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END) IS NULL THEN  ");
		sql.append(" l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount',");
		sql.append(" DATE_FORMAT(l.create_date,'%Y-%m-%d') AS 'create_date',op.province_name ");
		sql.append(" ,(SELECT d.item_name FROM pub_dditem d WHERE d.DD_NO = 9944 AND d.item_code = l.repay_mentway ) as repay_mentway ");
		sql.append(" ,part.brand_name,part.partner_name"); 
		sql.append(" from o2o_loanapply l LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null ");
		sql.append(" LEFT JOIN o2o_platform_regional op ON op.platform_id = l.ltd LEFT JOIN o2o_loanapply_expand e ");
		sql.append(" on e.loanapply_no=l.loanapply_no JOIN o2o_zx zx on u.zx_no = zx.id ");
		sql.append(" LEFT JOIN (SELECT opi.id,obr.brand_name,opi.partner_name FROM o2o_brand_info AS obr ");
		sql.append(" INNER JOIN o2o_partner_info AS opi ON obr.id = opi.brand_id) as part on part.id = l.partner_id ");
		sql.append(" where  l.islock='N' and l.loanapply_no=u.loanapply_no ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(payMentSearchForm.getUsersno())) {
			sql.append(" or l.operator_no in ("+payMentSearchForm.getUsersno()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		if (StringUtil.hasText(payMentSearchForm.getBillCode())) {
			sql.append(" and l.bill_code like ?");
			pageQuery.addLikeParam(payMentSearchForm.getBillCode());
		}
		if (StringUtil.hasText(payMentSearchForm.getLoanType())) {
			sql.append(" and l.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(payMentSearchForm.getLoanType().contains(",")){
				loanTypeA = payMentSearchForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+payMentSearchForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(payMentSearchForm.getPayMentStatus())) {
			if ("O".equals(payMentSearchForm.getPayMentStatus())) {
				sql.append(" and l.wf_type in ('YQ','DC','HN','O')");
			}else if("ZXSX".equals(payMentSearchForm.getPayMentStatus())){
				sql.append(" and l.credit_status=0");
			} else {
				sql.append(" and l.wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (payMentSearchForm.getPayMentStatus().contains(",")) {
					wfTypeA = payMentSearchForm.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + payMentSearchForm.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				sql.append(wfType);
				sql.append(" ) ");
			}
		}
		if (StringUtil.hasText(payMentSearchForm.getOperatorName())) {
			sql.append(" and l.operator_name like ?");
			pageQuery.addLikeParam(payMentSearchForm.getOperatorName());
		}
		if (StringUtil.hasText(payMentSearchForm.getUserSurname())) {
			sql.append(" and u.user_full_name like ?");
			pageQuery.addLikeParam(payMentSearchForm.getUserSurname());
		}
		if (StringUtil.hasText(payMentSearchForm.getBankType())) {
			sql.append(" and zx.zx_bank = ?");
			pageQuery.addParam(payMentSearchForm.getBankType());
		}
		if (StringUtil.hasText(payMentSearchForm.getBankloanname())) {
			sql.append(" and l.bank_loan_name = ?");
			pageQuery.addParam(payMentSearchForm.getBankloanname());
		}
		if (StringUtil.hasText(payMentSearchForm.getLoadName())) {
			sql.append(" and l.ltd_name like ?");
			pageQuery.addLikeParam(payMentSearchForm.getLoadName());
		}
		if (StringUtil.hasText(payMentSearchForm.getBuybackName())) {
			sql.append(" and l.buyback_name like ?");
			pageQuery.addLikeParam(payMentSearchForm.getBuybackName());
		}
		if (StringUtil.hasText(payMentSearchForm.getStartTime())) {
			sql.append(" and l.create_date >= ? ");
			pageQuery.addParam(payMentSearchForm.getStartTime());
		}
		if (StringUtil.hasText(payMentSearchForm.getEndTime())) {
			sql.append(" and l.create_date <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(payMentSearchForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(payMentSearchForm.getOperationModel())) {
			sql.append(" and l.operation_model = ? ");
			pageQuery.addParam(payMentSearchForm.getOperationModel());
		}
		//大区查询
		/*if(!StringUtil.isEmpty(payMentSearchForm.getRegionalName())){
			sql.append(" AND op.regional_name  = ?");
			pageQuery.addParam(payMentSearchForm.getRegionalName());
		}*/
		if (!StringUtil.isEmpty(payMentSearchForm.getHostProvince())) {
			sql.append(" AND  op.province_code  = ? ");
			pageQuery.addParam(payMentSearchForm.getHostProvince());
		}
		if (!StringUtil.isEmpty(payMentSearchForm.getOtoBzjGlf())) {
			
			if (payMentSearchForm.getOtoBzjGlf().contains("NEW")) {
				sql.append(" AND  (e.bill_status is null or t.bill_status in ( ");
			} else {
				sql.append(" AND  e.bill_status in ( ");
			}
			
			// ******************** loan-12380 贷款申请查询中的缴费状态改为多选查询
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(payMentSearchForm.getOtoBzjGlf().contains(",")){
				loanTypeA = payMentSearchForm.getOtoBzjGlf().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+payMentSearchForm.getOtoBzjGlf()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(" ) ");
			if (payMentSearchForm.getOtoBzjGlf().contains("NEW")) {
				sql.append(" ) ");
			}
			//***********************
		}
		if(!StringUtil.isEmpty(payMentSearchForm.getPartnerId())){
			sql.append(" AND part.id in (");
			sql.append(payMentSearchForm.getPartnerId());
			sql.append(")");
		}
		sql.append(" order by l.create_date	desc ");
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
}
